﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data;
using System.Data.SQLite;
using System.Text.RegularExpressions;

namespace PTCScheduler.EditMenu_Pages
{
    /// <summary>
    /// Interaction logic for UpdateCounselor_Page.xaml
    /// </summary>
    public partial class UpdateCounselor_Page : Page,ScheduleReport
    {
        //SQLite connection for the main window page
        private SQLiteConnection conn;

        //SQLite data dapter for main window page
        private SQLiteDataAdapter da;

        //SQLite database connection string
        private String connString = SystemQueries.dbConnString();

        //SQLite transaction object
        private SQLiteTransaction transaction;

        //Array old data
        private String[] arrOldData = new String[3];

        //Empty Constructor. Initializes components.
        public UpdateCounselor_Page()
        {
            InitializeComponent();

            //Open connection.
            dbConn();

            //Fill datagridview.
            queryResults();
        }

        //Method to clear the textfields of new Counselor form.
        private void ClearForm()
        {
            //Clear text fields.
            txtCounselorFName.Clear();
            txtCounselorLName.Clear();
            txtCounselorEmail.Clear();

            //Refocus to Counselor first name textbox.
            txtCounselorFName.Focus();
        }

        //Method to hide all labels.
        public void HideAllLabels()
        {
            //Hide Labels.
            lblCounselorSuccess.Visibility = Visibility.Hidden;
            lblValidEmail.Visibility = Visibility.Hidden;
            lblProvideAllInfo.Visibility = Visibility.Hidden;
            lblNoCounselors.Visibility = Visibility.Hidden;
            lblCounselorExists.Visibility = Visibility.Hidden;
        }

        //Method to check for valid email.
        public Boolean validEmail(String email)
        {
            if (email.Contains("@") && email.Contains("."))
            {
                return true;
            }
            return false;
        }

        //Method to refresh database with new results.
        private void RefreshDataGridView()
        {
            //Unregister selection changed event to fill data grid.
            dataGridCounselor.SelectionChanged -= dataGridCounselor_SelectionChanged;

            //Open connection and update dataview.
            dbConn();
            queryResults();

            //Register selection changed event to handle selection changes.
            dataGridCounselor.SelectionChanged += dataGridCounselor_SelectionChanged;
        }

        //Method to set textboxes to values in data row view.
        private void setTextBoxes(DataRowView row)
        {
            //Set text properties.
            txtCounselorFName.Text = row["First Name"].ToString();
            txtCounselorLName.Text = row["Last Name"].ToString();
            txtCounselorEmail.Text = row["Email"].ToString();

            //Put the old data into the old data array.
            arrOldData[0] = row["First Name"].ToString();
            arrOldData[1] = row["Last Name"].ToString();
            arrOldData[2] = row["Email"].ToString();
        }



        //Update Counselor string.
        private String updateCounselorString(int selectedrow)
        {
            //Since the 0 column is not shown then we must add 1 to update selected row.
            return @"UPDATE counselors " +
                    "SET fname = '" + txtCounselorFName.Text + "', lname = '" + txtCounselorLName.Text + "', " +
                            "email = '" + txtCounselorEmail.Text + "' " +
                     "WHERE fname = '" + arrOldData[0] + "' AND lname = '" + arrOldData[1] + "' AND email = '" + arrOldData[2] + "' ";
        }

        //Input data from datagridview into textboxes to be updated.
        private void dataGridCounselor_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {
            //Create data grid view row object.
            DataRowView row = (DataRowView)dataGridCounselor.SelectedItems[0];

            //Retrieve column values and insert into textboxes.
            setTextBoxes(row);
        }

        //Connect to the database.
        public void dbConn()
        {
            //Create connection.
            conn = new SQLiteConnection(connString);

            //Open connection
            conn.Open();
        }

        //Query the results and fill datagridview.
        public void queryResults()
        {
            //Retrieve the student table query string from the SystemQueries class. 
            String sql = SystemQueries.counselorTableQueryString();

            //Create dataset to fill with the results of the query. 
            DataSet ds = new DataSet(); //System.Data assembly

            //User SQLiteDataAdapter to retrieve results. 
            da = new SQLiteDataAdapter(sql, connString);

            //Fill the dataset with the results. 
            da.Fill(ds);

            //If there are no records then prompt user that there are no scheduled
            //conferences for today. Otherwise just display records. 
            if (ds.Tables[0].Rows.Count == 0)
            {
                lblNoCounselors.Visibility = Visibility.Visible;
            }
            else
            {
                lblNoCounselors.Visibility = Visibility.Hidden;
            }

            //Display results in datagrid view. 
            dataGridCounselor.ItemsSource = ds.Tables[0].DefaultView;

            //Close database connection.
            conn.Close();
        }

        //Handle update button click.
        private void btnUpdate_Click(object sender, RoutedEventArgs e)
        {
            //Hide all labels by default.
            HideAllLabels();
            string bounds = @"^[a-zA-Z]+$";
            try//Try catch to catch format exceptions.
            {
                //If missing fields throw exception.
                if ((txtCounselorFName.Text == String.Empty) || (txtCounselorLName.Text == String.Empty) || (txtCounselorEmail.Text == String.Empty))
                {
                    throw new MissingFieldException();
                }

                if (!Regex.IsMatch(txtCounselorFName.Text, bounds) && !Regex.IsMatch(txtCounselorLName.Text, bounds) && Regex.IsMatch(txtCounselorEmail.Text, bounds))
                {
                    throw new FormatException();
                }


                //If invalid email throw exception.
                if (validEmail(txtCounselorEmail.Text) == false)
                {
                    throw new FormatException();
                }

                //Open connection
                dbConn();

                //Set the SQLiteTransaction
                transaction = conn.BeginTransaction();

                //Create the SQLite command and set its properties
                SQLiteCommand command = new SQLiteCommand();
                command.Transaction = transaction;
                command.CommandText = this.updateCounselorString(dataGridCounselor.SelectedIndex);
                command.ExecuteNonQuery();

                //Commit the transaction
                transaction.Commit();

                //Display successful transaction message.
                lblCounselorSuccess.Visibility = Visibility.Visible;

                //Clear form.
                ClearForm();

                //Update datagridview.
                RefreshDataGridView();
            }
            catch (Exception ex)
            {
                if (ex is FormatException)
                {
                    //Display warning label.
                    lblValidEmail.Visibility = Visibility.Visible;
                }
                else if (ex is MissingFieldException)
                {
                    //Display information label.
                    lblProvideAllInfo.Visibility = Visibility.Visible;
                }
                else if (ex is SQLiteException)
                    lblCounselorExists.Visibility = Visibility.Visible;
            }
            finally
            {
                //Close and dispose of connection.
                conn.Close();
                conn.Dispose();
            }//End Try-Catch
        }//End Update Handler


    }
}
